package com.bluesky.light.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.bluesky.light.client.Cart;
import com.bluesky.light.client.CartItem;
import com.bluesky.light.order.OrderService;
import com.bluesky.light.order.TreatItem;
import com.bluesky.light.order.TreatOrder;
import com.bluesky.light.staff.Staff;
import com.bluesky.light.util.DB;

/**
 * 用戶對數據庫增刪查改等, 對數據庫的基本操作
 * 
 * @author
 * 
 */

public class UserDAO {

	/**
	 * @param u
	 * 
	 *            插入一個新用戶到數據庫
	 */
	public void add(User u) {
		Connection conn = DB.getConn();
		// for oracle
		// String sql =
		// "insert into userinfo (no, username, pwd, name, id, sex, birth, phone, addr, rdate) values (sq_id.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		// for mysql
		String sql = "insert into t_user (username, pwd, name, idcard, sex, birth, phone, addr, rdate) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setString(1, u.getUsername());
			pstmt.setString(2, u.getPwd());
			pstmt.setString(3, u.getName());
			pstmt.setString(4, u.getIdcard());
			pstmt.setString(5, u.getSex());
			pstmt.setTimestamp(6, new Timestamp(u.getBirth().getTime()));
			pstmt.setString(7, u.getPhone());
			pstmt.setString(8, u.getAddr());
			pstmt.setTimestamp(9, new Timestamp(u.getRdate().getTime()));

			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * @param no
	 * @return b false代表刪除用戶失敗, true代表刪除用戶成功。
	 * 
	 *         根據no刪除用戶
	 */
	public boolean delete(int id) {
		boolean b = false;

		Connection conn = DB.getConn();
		String sql = "delete from t_user where id = " + id;
		Statement stmt = DB.getStatement(conn);

		try {
			DB.excuteUpdate(stmt, sql);
			b = true;
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}
		return b;
	}

	/**
	 * @param u
	 * 
	 *            更新用戶的個人資料
	 */
	public void update(User u) {
		Connection conn = DB.getConn();
		String sql = "update t_user set name = ?, idcard = ?, sex = ?, birth = ?, phone = ?, addr = ? where id = ?";
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setString(1, u.getName());
			pstmt.setString(2, u.getIdcard());
			pstmt.setString(3, u.getSex());
			pstmt.setTimestamp(4, new Timestamp(u.getBirth().getTime()));
			pstmt.setString(5, u.getPhone());
			pstmt.setString(6, u.getAddr());
			pstmt.setInt(7, u.getId());

			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * @param u
	 * 
	 *            更改用戶的密碼
	 */
	public void updatePwd(User u) {
		Connection conn = DB.getConn();
		String sql = "update t_user set pwd = ? where id = ?";
		// System.out.println(u.getNo() + u.getPwd());
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setString(1, u.getPwd());
			pstmt.setInt(2, u.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * @param username
	 * @param pwd
	 * @return
	 * @throws UserNotFoundException
	 * @throws PasswordNotCorrectException
	 * 
	 *             驗證用戶所輸入的username 是否存在, 如果不存在, 作異常返回, 如果用戶存在,
	 *             則驗證這個用戶所對應的密碼是否正確, 如果密碼不正確, 處理密碼不正確的異常, 如果密碼正確,
	 *             則返回這個用戶的所有個人資料。
	 */
	public User check(String username, String pwd)
			throws UserNotFoundException, UserPasswordNotCorrectException {

		User u = null;
		Connection conn = DB.getConn();
		System.out.println(conn);
		Statement stmt = DB.getStatement(conn);
		String sql = "select * from t_user where username = '" + username + "'";
		ResultSet rs = DB.getResultSet(stmt, sql);

		try {
			if (!rs.next()) {
				throw new UserNotFoundException("User not found:" + username);
			} else {
				if (!pwd.equals(rs.getString("pwd"))) {
					throw new UserPasswordNotCorrectException(
							"Password Not Correct!");
				}
				u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPwd(rs.getString("pwd"));
				u.setName(rs.getString("name"));
				u.setIdcard(rs.getString("idcard"));
				u.setSex(rs.getString("sex"));
				u.setBirth(rs.getTimestamp("birth"));
				u.setPhone(rs.getString("phone"));
				u.setAddr(rs.getString("addr"));
				u.setRdate(rs.getTimestamp("rdate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return u;
	}

	/**
	 * @param users
	 * @return totalRecords 獲得用戶的總人數
	 * 
	 *         從數據庫取出所有用戶的資料
	 */
	public int getUsers(List<User> users) {
		int totalRecords = -1;

		Connection conn = DB.getConn();
		String sql = "select * from t_user";
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = DB.getResultSet(stmt, sql);

		Statement stmtCount = DB.getStatement(conn);
		ResultSet rsCount = DB.getResultSet(stmtCount,
				"select count(*) from t_user");

		try {
			/* 獲得用戶的總人數 */
			rsCount.next();
			totalRecords = rsCount.getInt(1);

			while (rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPwd(rs.getString("pwd"));
				u.setName(rs.getString("name"));
				u.setIdcard(rs.getString("idcard"));
				u.setSex(rs.getString("sex"));
				u.setBirth(rs.getTimestamp("birth"));//
				u.setPhone(rs.getString("phone"));
				u.setAddr(rs.getString("addr"));
				u.setRdate(rs.getTimestamp("rdate"));
				users.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rsCount);
			DB.close(stmtCount);
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return totalRecords;
	}

	/*
	 * public static int getUsers(List<User> users, int pageNo, int pageSize) {
	 * int totalRecords = -1;
	 * 
	 * Connection conn = DB.getConn(); String sql =
	 * "select * from userinfo limit " + (pageNo - 1) * pageSize + "," +
	 * pageSize; Statement stmt = DB.getStatement(conn); ResultSet rs =
	 * DB.getResultSet(stmt, sql);
	 * 
	 * Statement stmtCount = DB.getStatement(conn); ResultSet rsCount =
	 * DB.getResultSet(stmtCount, "select count(*) from userinfo");
	 * 
	 * try { rsCount.next(); totalRecords = rsCount.getInt(1);
	 * 
	 * while (rs.next()) { User u = new User(); u.setId(rs.getString("userid"));
	 * u.setUsername(rs.getString("username")); u.setPwd(rs.getString("pwd"));
	 * u.setSex(rs.getString("sex")); u.setBirth(rs.getInt("birth"));
	 * u.setPhone(rs.getString("phone")); u.setAddr(rs.getString("addr"));
	 * u.setRdate(rs.getTimestamp("rdate")); u.setNo(rs.getInt("no"));
	 * users.add(u); } } catch (SQLException e) { e.printStackTrace(); } finally
	 * { DB.close(rsCount); DB.close(stmtCount); DB.close(rs); DB.close(stmt);
	 * DB.close(conn); }
	 * 
	 * return totalRecords; }
	 */

	/**
	 * @param no
	 * @return
	 * 
	 *         根據no 取出用戶的數據
	 */
	public User loadById(int id) {
		User u = new User();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DB.getConn();
			String sql = "select * from t_user where id = " + id;
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);

			if (rs.next()) {
				u.setId(id);
				u.setUsername(rs.getString("username"));
				u.setPwd(rs.getString("pwd"));
				u.setName(rs.getString("name"));
				u.setIdcard(rs.getString("idcard"));
				u.setSex(rs.getString("sex"));
				u.setBirth(rs.getTimestamp("birth"));//
				u.setPhone(rs.getString("phone"));
				u.setAddr(rs.getString("addr"));
				u.setRdate(rs.getTimestamp("rdate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return u;
	}

	/**
	 * 醫生跟病人看診之後, 改變病人的看病狀態為 trd(已為病人看病)
	 * @param c
	 * @param u
	 * @param s
	 * @return
	 * 
	 */
	public int acceptTreatment(Cart c, User u, Staff s) {
		TreatOrder to = new TreatOrder();
		to.setId(c.getToid());
		to.setUser(u);
		to.setSymptom(c.getSymptom());
		to.setStaffid(s.getId());
		to.setStatus("trd");
		to.setoDate(new Date());
		List<TreatItem> treatItems = new ArrayList<TreatItem>();
		List<CartItem> cartItems = c.getItems();

		for (int i = 0; i < cartItems.size(); i++) {
			TreatItem ti = new TreatItem();
			CartItem ci = cartItems.get(i);
			ti.setProduct(ci.getProduct());
			ti.setCount(ci.getCount());
			treatItems.add(ti);
		}

		to.setItems(treatItems);
		return OrderService.getInstance().update(to);
	}

	/**
	 * 根據用戶的 身份證號碼 在數據庫查詢用戶的個人資料
	 * 
	 * @param idcard
	 * @return
	 * 
	 */
	public User loadByIdcard(String idcard) {
		User u = new User();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DB.getConn();
			String sql = "select * from t_user where idcard = '" + idcard + "'";
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);

			if (rs.next()) {
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPwd(rs.getString("pwd"));
				u.setName(rs.getString("name"));
				u.setIdcard(rs.getString("idcard"));
				u.setSex(rs.getString("sex"));
				u.setBirth(rs.getTimestamp("birth"));
				u.setPhone(rs.getString("phone"));
				u.setAddr(rs.getString("addr"));
				u.setRdate(rs.getTimestamp("rdate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return u;
	}

	/**
	 * @param u
	 * @param opdate
	 * 
	 *            將用戶的預約申請存入數據庫
	 */
	public void bookTreatment(User u, Date opdate) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = DB.getConn();
		String sql = "insert into t_treatorder (userid, odate, status) values (?, ?, ?)";
		pstmt = DB.prepare(conn, sql);

		try {
			pstmt.setInt(1, u.getId());
			pstmt.setTimestamp(2, new Timestamp(opdate.getTime()));
			pstmt.setString(3, "bok");

			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

}
